#! /usr/bin/python
### Author DeanChen  deanchen@juniper.net
import xlrd
import MySQLdb
import sys
import os


file_name = raw_input("please enter the file path and name:")
workbook = xlrd.open_workbook(file_name)
sheet_count = len(workbook.sheets())
print sheet_count

database = MySQLdb.Connect(host = 'localhost', user = 'root', passwd = 'netscreen', db = 'regression', charset = 'utf8')
cursor = database.cursor()

#cursor.execute("drop table if exists resources")

#resources_table = """create table regression_test(
#                     category varchar(100) not null,
#                     description varchar(100),
#                     SN varchar(100),
#                     status varchar(100),
#                     PR_num varchar(100),
#                     Rec_date varchar(100),
#                     location varchar(100),
#                     comment varchar(200),
#                     submission_date varchar(100))"""
#cursor.execute(resources_table)

for id in range(0,sheet_count):
    sheet = workbook.sheet_by_index(id)
    nrow = sheet.nrows
    print nrow

#    database = MySQLdb.Connect(host = 'localhost', user = 'deanchen', passwd = '', db = 'sbu_pdt', charset = 'utf8')
#    cursor = database.cursor()
#    cursor.execute("drop table if exists resources")
    query = """insert into resources_chassis(Category,Description,SN,Status,PR,RecDate,Location,Name,Owner,Ucs,Comment) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""

    for r in range (1,nrow):
        Category = sheet.cell(r,0).value
        Description = sheet.cell(r,1).value
        SN = sheet.cell(r,2).value
        Status = sheet.cell(r,3).value
        PR = sheet.cell(r,4).value
        RecDate = sheet.cell(r,5).value
        Location = sheet.cell(r,6).value
        Name = sheet.cell(r,7).value
        Owner = sheet.cell(r,8).value
        Ucs = sheet.cell(r,9).value
        Comment = sheet.cell(r,10).value
        values = (Category,Description,SN,Status,PR,RecDate,Location,Name,Owner,Ucs,Comment)
        cursor.execute(query,values)

cursor.close()
database.commit()
database.close()

print "Done !!! "
